package beans;

import javax.ejb.Stateless;
import javax.persistence.EntityManager;
import javax.persistence.NoResultException;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import entities.Categorie;
import java.util.Collection;

@Stateless
public class CategoryFacade extends AbstractFacade<Categorie> {
    @PersistenceContext(unitName = "ProjetAppliWebPU")
    private EntityManager em;

    @Override
    protected EntityManager getEntityManager() {
        return em;
    }

    public CategoryFacade() {
        super(Categorie.class);
    }

    public Categorie getByName(String name) {
        Query query = em.createQuery("SELECT c FROM Categorie c WHERE c.name = :name");
        query.setParameter("name", name);

        try {
            return (Categorie) query.getSingleResult();
        } catch(NoResultException e) {
            return null;
        }
    }

    public Categorie getOrCreate(String name) {
        Categorie category = getByName(name);

        if (category == null) {
            category = new Categorie(name);
            create(category);
        }

        return category;
    }

    public Collection<Categorie> getAll() {
        Query query = em.createQuery("SELECT c FROM Categorie c ORDER BY c.name");
        return query.getResultList();
    }

    public Collection<Categorie> get(int first, int max) {
        return get(first, max, "name", "ASC");
    }

    public Collection<Categorie> get(int first, int max, String sort, String value) {
        Query query = em.createQuery("SELECT c FROM Categorie c ORDER BY c." + sort + " " + value);
        query.setFirstResult(first);
        query.setMaxResults(max);
        return query.getResultList();
    }

    public Collection<Object> getOrderByTotalVideoGames(int first, int max, String value) {
        Query query = em.createNativeQuery(
                "SELECT c.id, COUNT(*) number "
                + "FROM Categorie c, JeuVideo jv "
                + "WHERE c.id = jv.categorie_id "
                + "GROUP BY c.id "
                + "ORDER BY number " + value,
                "recupererParTotalJeuxVideo");
        query.setFirstResult(first);
        query.setMaxResults(max);
        return query.getResultList();
    }

    public Collection<Categorie> getByKeyword(String keyword) {
        Query q = em.createQuery("SELECT c FROM Categorie c WHERE LOWER(c.name) LIKE :keyword ORDER BY c.name");
        q.setParameter("keyword", "%" + keyword.toLowerCase() + "%");
        return q.getResultList();
    }

    public Collection<String> getNames() {
        Query q = em.createQuery("SELECT DISTINCT c.name FROM Categorie c ORDER BY c.name");
        return q.getResultList();
    }
}